%macro unmask_tins;

	/****************************************************
	*													*
	*				Bring in the SOI Data				*
	*													*
	****************************************************/


	proc printto log = "&logdir/unmask_tins.txt" new;
	run;
		
	
	PROC IMPORT OUT= soi
	            DATAFILE= "&indir./soi_2019.dta" 
	            DBMS=DTA REPLACE;
	RUN;


	data soi;
		set soi(rename=(soi_prim_unmasked_tin = prim_unmasked_tin
						soi_sec_unmasked_tin = sec_unmasked_tin));
		if soi_fil_stat = 3 then sec_unmasked_tin = 0;

		prim_ssn = put(prim_unmasked_tin,z9.);
		sec_ssn = put(sec_unmasked_tin,z9.);
		mars = soi_fil_stat;
		keep prim_ssn sec_ssn mars prim_unmasked_tin sec_unmasked_tin;
	run;
	
	proc sort data = soi;
		by prim_ssn sec_ssn;
	run;
	



	
		


		data _null_;
			set soi;
			file "&tmpdir./tinlist" dlm=',';
				put prim_ssn sec_ssn; 
		run;


		/************************************************
		*												*
		*					Main SQL call				*
		*												*
		************************************************/
		options errors = 0;
				proc sql noprint;
				[connection details redacted]
					escapes off quotes off delimited by ',') by iq;
					create table sample as
					select * from connection to iq (
						select
							a.prim_ssn,
							a.sec_ssn,
							b.[redacted] as prim_tin,
							coalesce(c.[redacted], 0) as sec_tin
						from #tinlist as a
							inner join [redacted] as b
								on(a.prim_ssn = b.[redacted])
							left outer join [redacted] as c
								on(a.sec_ssn = c.[redacted])
						order by a.prim_ssn,
							a.sec_ssn

					);
					disconnect from iq;
					quit;

		proc printto;
		run;


		data rlib.soi;
			merge sample(in=a) soi;
			by prim_ssn sec_ssn;
			if a;
		run;

%mend;
